﻿CREATE TABLE [dbo].[Location] (
    [Id]               UNIQUEIDENTIFIER CONSTRAINT [DF_Location_Id] DEFAULT (newid()) NOT NULL,
    [ParentLocationId] UNIQUEIDENTIFIER NULL,
    [LocationTypeId]   UNIQUEIDENTIFIER NOT NULL,
    [Name]             NVARCHAR (128)   NOT NULL,
    [DateActive]       DATE             CONSTRAINT [DF_Location_DateActive] DEFAULT (getdate()) NOT NULL,
    [DateInactive]     DATE             NULL,
    [Description]      NVARCHAR (MAX)   CONSTRAINT [DF_Location_Description] DEFAULT ('') NOT NULL,
    [CompanyId]        UNIQUEIDENTIFIER NOT NULL,
    CONSTRAINT [PK_Location] PRIMARY KEY CLUSTERED ([Id] ASC),
    CONSTRAINT [FK_Location_Company] FOREIGN KEY ([CompanyId]) REFERENCES [dbo].[Company] ([Id]),
    CONSTRAINT [FK_Location_Location] FOREIGN KEY ([ParentLocationId]) REFERENCES [dbo].[Location] ([Id]),
    CONSTRAINT [FK_Location_LocationType] FOREIGN KEY ([LocationTypeId]) REFERENCES [dbo].[LocationType] ([Id])
);




GO
CREATE UNIQUE NONCLUSTERED INDEX [IX_Location]
    ON [dbo].[Location]([CompanyId] ASC, [Name] ASC);

